import pandas as pd
from pathlib import Path

# Adjust the path to your data directory
raw_csv = Path(__file__).parent / "ni-census21-hh_tenure_agg7+hh_religion.csv"
df = pd.read_csv(raw_csv)

# Identify relevant columns dynamically
tenure_label_col = [c for c in df.columns if "Tenure" in c and "Label" in c][0]
religion_code_col = [c for c in df.columns if "Religion" in c and "Code" in c][0]
count_col = [c for c in df.columns if c.lower() == "count" or "Count" in c][0]

owner_rows = [
    "Owner occupied: Owns outright",
    "Owner occupied: Owns with a mortgage or loan or shared ownership"
]
owners = df[df[tenure_label_col].isin(owner_rows)]

cath_codes = [1, 5]
prot_codes = [2, 6]

totals = df.groupby(religion_code_col)[count_col].sum()
owner_totals = owners.groupby(religion_code_col)[count_col].sum()

summary = pd.DataFrame({
    "Group": ["Catholic", "Protestant+Other Christian"],
    "Owned": [owner_totals.loc[cath_codes].sum(), owner_totals.loc[prot_codes].sum()],
    "Total": [totals.loc[cath_codes].sum(), totals.loc[prot_codes].sum()]
})
summary["Ownership_%"] = (summary["Owned"] / summary["Total"] * 100).round(1)

summary.to_csv(Path(__file__).parent / "ownership_rates_2021.csv", index=False)
print(summary)